import pymysql
from dbutils.pooled_db import PooledDB
from com.util.loggingUtil import Logs

from com.util.configUtil import Conf

# 数据库工厂
dbFactory = {}


def initPool(dbconfName):
    _maxconnections = int(Conf.MYSQLDB_CONF.getConfVal(dbconfName, "maxConnections"))
    _mincached = int(Conf.MYSQLDB_CONF.getConfVal(dbconfName, "mincached"))
    _maxcached = int(Conf.MYSQLDB_CONF.getConfVal(dbconfName, "maxcached"))
    _host = Conf.MYSQLDB_CONF.getConfVal(dbconfName, "host")
    _port = int(Conf.MYSQLDB_CONF.getConfVal(dbconfName, "port"))
    _user = Conf.MYSQLDB_CONF.getConfVal(dbconfName, "username")
    _password = Conf.MYSQLDB_CONF.getConfVal(dbconfName, "password")
    _database = Conf.MYSQLDB_CONF.getConfVal(dbconfName, "database")
    dbPooled = PooledDB(creator=pymysql,
                        maxconnections=_maxconnections,
                        mincached=_mincached,
                        maxcached=_maxcached,
                        blocking=True,
                        cursorclass=pymysql.cursors.DictCursor,
                        host=_host,
                        port=_port,
                        user=_user,
                        password=_password,
                        database=_database,
                        setsession=['SET AUTOCOMMIT = 1'])
    dbFactory[dbconfName] = dbPooled


class MysqlConnection(object):
    """
    mysql链接池:
    pymysql autoCommit默认为false 导致无法查询新增的数据，每次查询需提交commit或者初始化时设置autoCommit
    pymysql:mysql的Python连接包
    DBUtils:连接池组件
    configparser：配置文件模块
    mysql-connector-python：驱动包
    https://blog.csdn.net/u013444182/article/details/111229362
    """

    def __init__(self, dbName='mysql-test'):
        """
        见/conf/mysqldb.conf配置
        使用方法:MysqlConnection('mysql-test')/MysqlConnection()
        :param dbName:
        """
        if not dbFactory.__contains__(dbName):
            initPool(dbName)
        self.connect: pymysql.connect = dbFactory[dbName].connection()
        self.cursor = self.connect.cursor()

    def execute(self, sql, param=None):
        """
        基础更新、插入、删除操作
        :param sql:
        :param param:
        :return: 受影响的行数
        """
        ret = None
        try:
            if param == None:
                ret = self.cursor.execute(sql)
            else:
                ret = self.cursor.execute(sql, param)
            self.commit()
        except TypeError as te:
            Logs().debug("类型错误")
            Logs().exception(te)
        return ret

    def query(self, sql, param=None):
        """
        查询数据库
        :param sql: 查询SQL语句
        :param param: 参数
        :return: 返回集合
        """
        self.cursor.execute(sql, param)
        result = self.cursor.fetchall()
        self.commit()
        return result

    def queryOne(self, sql, param=None):
        """
        查询数据返回第一条
        :param sql: 查询SQL语句
        :param param: 参数
        :return: 返回第一条数据的字典
        """
        result = self.query(sql, param)
        if result:
            return result[0]
        else:
            return None

    def listByPage(self, sql, current_page, page_size, param=None):
        """
        分页查询当前表格数据
        :param sql: 查询SQL语句
        :param current_page: 当前页码
        :param page_size: 页码大小
        :param param:参数
        :return:
        """
        countSQL = "select count(*) ct from (" + sql + ") tmp "
        Logs().debug("统计SQL:{}".format(sql))
        countNum = self.count(countSQL, param)
        offset = (current_page - 1) * page_size
        totalPage = int(countNum / page_size)
        if countNum % page_size > 0:
            totalPage = totalPage + 1
        pagination = {"current_page": current_page, "page_size": page_size, "count": countNum, "total_page": totalPage}
        querySql = "select * from (" + sql + ") tmp limit %s,%s"
        Logs().debug("查询SQL:{}".format(querySql))
        # 判断是否有参数
        if param == None:
            # 无参数
            pagination["data"] = self.query(querySql, (offset, page_size))
        else:
            # 有参数的情况,此时需要判断参数是元祖还是字典
            if isinstance(param, dict):
                # 字典的情况,因此需要添加字典
                querySql = "select * from (" + sql + ") tmp limit %(tmp_offset)s,%(tmp_pageSize)s"
                param["tmp_offset"] = offset
                param["tmp_pageSize"] = page_size
                pagination["data"] = self.query(querySql, param)
            elif isinstance(param, tuple):
                # 元祖的方式
                listtp = list(param)
                listtp.append(offset)
                listtp.append(page_size)
                pagination["data"] = self.query(querySql, tuple(listtp))
            else:
                # 基础类型
                listtp = []
                listtp.append(param)
                listtp.append(offset)
                listtp.append(page_size)
                pagination["data"] = self.query(querySql, tuple(listtp))
        return pagination

    def count(self, sql, param=None):
        """
        统计当前表记录行数
        :param sql: 统计SQL语句
        :param param: 参数
        :return: 当前记录行
        """
        ret = self.queryOne(sql, param)
        self.commit()
        count = None
        if ret:
            for k, v in ret.items():
                count = v
        return count

    def insert(self, sql, param=None):
        """
        数据库插入
        :param sql: SQL语句
        :param param: 参数
        :return: 受影响的行数
        """
        return self.execute(sql, param)

    def update(self, sql, param=None):
        """
        更新操作
        :param sql: SQL语句
        :param param: 参数
        :return: 受影响的行数
        """
        return self.execute(sql, param)

    def delete(self, sql, param=None):
        """
        删除操作
        :param sql: 删除SQL语句
        :param param: 参数
        :return: 受影响的行数
        """
        return self.execute(sql, param)

    def batch(self, sql, param=None):
        """
        批量插入
        :param sql: 插入SQL语句
        :param param: 参数
        :return: 受影响的行数
        """
        return self.cursor.executemany(sql, param)

    def commit(self, param=None):
        """
        提交数据库
        :param param:
        :return:
        """
        if param == None:
            self.connect.commit()
        else:
            self.connect.rollback()

    def close(self):
        """
        关闭数据库连接
        :return:
        """
        if self.cursor:
            self.cursor.close()
        if self.connect:
            self.connect.close()
        Logs().debug("释放数据库连接")
        return None
